A front-end server provides users with access to backend federated data sources (i.e., federated databases). Users can pose complex queries that seek data from such federated data sources. The front-end server is also herein referred to as a central controlling server. The complex queries may contain one or more subqueries.
A subquery is a specialized part of a SQL statement. For example, a subquery is a SELECT query that returns a single value and is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery is also referred to as an inner query, while the statement containing a subquery is also called an outer query.
It is cost prohibitive and inefficient to pull all the data from the associated backend federated databases to the front-end server in order to execute the given query at the front-end server. It is more efficient to send the query to the backend database servers for execution. However, it is often not possible to ship such queries in their entirety to the back-end federated data sources for multiple reasons.
For example,
1. The subqueries in the given query may reference data stored in two or more backend databases controlled by the front-end database server.
2. The subqueries may contain functions that can only be executed at the front-end server.
Consider the following subquery, which belongs to a typical class of queries generated by a front-end application such as a Marketing Automation application.
SELECTContacts.NameFROMMONSTER_FACTWHEREContacts.ROW_ID IN <BASE SEGMENT> ANDContacts.ROW_ID IN (SELECT Contacts.ROW_ID  FROM ROLE_F  WHERE Percentile (rcount (Rank (ROLE_F.“# of  Accounts”))) <.1AND Contacts.ROW_ID IN <BASE SEGMENT>ORDER BY Rank (ROLE_F.“# of Accounts”))
The above query cannot be shipped in its entirety to the associated backend database for execution because the query contains functions supported only at the front-end server. For example, assume that Percentile and Rcount functions are supported only at the front-end server.
If a query cannot be shipped in its entirety to the associated backend database as described above, then according to one approach, such a subquery can be executed at the front-end server by incorporating the literal values from the subquery results into the outer query. Such an approach is also referred to herein as the “expand inlist subquery processing strategy.” However, the expand inlist subquery processing strategy may not be feasible if the literal values exceed an allowable limit. For example, assume that the subquery contains more rows than the subquery row limit, which is a parameter that is configurable in the front-end controlling server. In such a case, one will not be able to use the expand inlist subquery processing strategy.
In view of the foregoing, an automated and efficient approach for enabling the front-end server to support subqueries that reference data from two or more associated backend databases, is needed, without adversely affecting the performance of the front-end application queries and without imposing undue maintenance/upgrade costs on existing front-end systems.